package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.HashMap;

public class Accessor
{

    Connection connection;
    Connector connector;
    boolean strictExceptionMode = true;

    public Accessor(Connector connector) throws Exception
    {
        this.connector = connector;
        this.connection = connector.waitForConnectionTask();
    }

    public void close() throws SQLException, Exception
    {
        try
        {
            if (connection.getAutoCommit() == false)
            {
                try
                {
                    connection.rollback();
                    connection.setAutoCommit(true);
                    throw new Exception(
                                    "Unfinished transaction. Rollback performed.");
                }
                catch (SQLException e)
                {
                    try
                    {
                        connection.close();
                        throw new Exception(
                                        "Unfinished transaction. Unable to rollback.",
                                        e);
                    }
                    catch (SQLException e2)
                    {
                        throw new Exception("Unable to close.", e2);
                    }
                }
            }
        }
        finally
        {
            connector.releaseConnection(connection);
        }
    }

    public void beginTransaction(String categoryOverride) throws Exception
    {
        beginTransaction(categoryOverride, true);
    }

    void closeConnectionAfterException(Exception e)
    {
        if (connection != null)
        {
            try
            {
                connection.rollback();
                connection.setAutoCommit(true);
            }
            catch (SQLException e1)
            {
                System.err
                                .println("Transaction rollback exception after execute exception");
            }
        }
        if (connection == null)
        {
            try
            {
                connection.close();
            }
            catch (SQLException e1)
            {
                System.err
                                .println("Exception Attempt to close connection after execute exception");
            }
        }
    }

    public void beginTransaction(String categoryOverride,
                    boolean strictExceptionMode) throws Exception
    {
        if (categoryOverride == null)
        {
            categoryOverride = connector.getName() + ".transaction";
        }
        this.strictExceptionMode = strictExceptionMode;
        connection.setAutoCommit(false);
    }

    public void commitTransaction() throws Exception
    {
        try
        {
            connection.commit();
            connection.setAutoCommit(true);
        }
        catch (SQLException e)
        {
            throw e;
        }
        finally
        {
            connection.close();
        }
    }

    public void rollbackTransaction() throws Exception
    {
        try
        {
            connection.rollback();
            connection.setAutoCommit(true);
        }
        catch (SQLException e)
        {

            throw e;
        }
        finally
        {
            connection.close();
        }
    }

    public int[] executeBatch(String categoryOverride, String... sqls)
                    throws SQLException
    {
        if (categoryOverride == null)
        {
            categoryOverride = connector.getName() + ".batch";
        }
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < sqls.length; i++)
        {
            if (i > 0)
            {
                sb.append(";");
            }
            sb.append(sqls[i]);
        }
        try
        {
            Statement stmt = connection.createStatement();
            for (String sql : sqls)
            {
                stmt.addBatch(sql);
            }
            try
            {
                stmt.setQueryTimeout(connector.getSelectTimeout());

                int[] results = stmt.executeBatch();

                /*
                 * int total = 0; for (int result : results) { if (result > 0) { total += result; }
                 * }
                 */
                return results;

            }
            finally
            {
                stmt.close();
            }
        }
        catch (SQLException e)
        {
            closeConnectionAfterException(e);
            throw e;
        }
    }

    public int[] executeBatchUpdate(String categoryOverride, String sql,
                    Object[][] objects) throws SQLException
    {
        if (categoryOverride == null)
        {
            categoryOverride = sql;
        }

        try
        {
            PreparedStatement stmt = connection.prepareStatement(sql);
            try
            {
                StringBuilder context = new StringBuilder();
                for (int i = 0; i < objects.length; ++i)
                {
                    if (i > 0)
                    {
                        context.append(',');
                    }
                    context.append('(');
                    Object[] objectList = objects[i];
                    context.append(setStatement(stmt, objectList));
                    context.append(')');
                    stmt.addBatch();
                }
                stmt.setQueryTimeout(connector.getUpdateTimeout());

                int[] results = stmt.executeBatch();

                /*
                 * int updated = 0; for (int result : results) { if (result > 0) { updated +=
                 * result; } }
                 */
                return results;
            }
            finally
            {
                stmt.close();
            }
        }
        catch (SQLException e)
        {
            closeConnectionAfterException(e);
            throw e;
        }
    }

    public int executeUpdate(String categoryOverride, String sql,
                    Object... objects) throws SQLException
    {
        if (categoryOverride == null)
        {
            categoryOverride = sql;
        }
        try
        {
            PreparedStatement stmt = connection.prepareStatement(sql);
            try
            {
                stmt.setQueryTimeout(connector.getUpdateTimeout());

            	setStatement(stmt, objects);
                int updated = stmt.executeUpdate();

                return updated;
            }
            finally
            {
                stmt.close();
            }
        }
        catch (SQLException e)
        {
            closeConnectionAfterException(e);
            throw e;
        }
    }

    public RowSet executeUpdateAndGetGeneratedKeys(String categoryOverride,
                    String sql, Object... objects) throws SQLException
    {
        if (categoryOverride == null)
        {
            categoryOverride = sql;
        }
        try
        {
            PreparedStatement stmt = connection.prepareStatement(sql,
                Statement.RETURN_GENERATED_KEYS);
            try
            {
            	setStatement(stmt, objects);
                stmt.setQueryTimeout(connector.getUpdateTimeout());

                stmt.executeUpdate();

                return new RowSet(this.connector, stmt.getGeneratedKeys());
            }
            finally
            {
                stmt.close();
            }
        }
        catch (SQLException e)
        {
            closeConnectionAfterException(e);
            throw e;
        }
        finally
        {
        }
    }

    public int executeUpdateCall(String categoryOverride, String sql,
                    Object... objects) throws SQLException
    {
        if (categoryOverride == null)
        {
            categoryOverride = sql;
        }
        try
        {
            CallableStatement stmt = connection.prepareCall(sql);
            try
            {
                stmt.setQueryTimeout(connector.getUpdateTimeout());
            	setStatement(stmt, objects);
                int updated = stmt.executeUpdate();

                return updated;
            }
            finally
            {
                stmt.close();
            }
        }
        catch (SQLException e)
        {
            closeConnectionAfterException(e);
            throw e;
        }
    }

    public RowSet execute(String categoryOverride, String sql,
                    Object... objects) throws SQLException
    {
        if (categoryOverride == null)
        {
            categoryOverride = sql;
        }
        try
        {
            PreparedStatement stmt = connection.prepareStatement(sql);
            try
            {
                setStatement(stmt, objects);
                stmt.setQueryTimeout(connector.getSelectTimeout());

                boolean result = stmt.execute();

                if (result == false)
                {
                    return null;
                }
                ResultSet rs = stmt.getResultSet();
                if (rs == null)
                {
                    return null;
                }
                try
                {
                    RowSet rowset = new RowSet(this.connector, rs);
                    return rowset;
                }
                finally
                {
                    rs.close();
                }
            }
            finally
            {
                stmt.close();
            }
        }
        catch (SQLException e)
        {
            closeConnectionAfterException(e);
            throw e;
        }
    }

    public HashMap<Integer, Integer> executeCall(String categoryOverride, String sql,
                    Object... objects) throws SQLException
    {
        if (categoryOverride == null)
        {
            categoryOverride = sql;
        }
        try
        {
            CallableStatement stmt = connection.prepareCall(sql);
            try
            {
                setStatement(stmt, objects);
                stmt.setQueryTimeout(connector.getSelectTimeout());

                //Out errorcode
                stmt.registerOutParameter(objects.length+1, Types.INTEGER);
                //Out clazzid
                stmt.registerOutParameter(objects.length+2, Types.INTEGER);
                
                boolean result = stmt.execute();

                if (result == false)
                {
                	// Return procedure result
                	HashMap<Integer, Integer> rst = new HashMap<Integer, Integer>();
                	rst.put(1, stmt.getInt(objects.length+1));
                	rst.put(2, stmt.getInt(objects.length+2));
                    return rst;
                }
                /**
                ResultSet rs = stmt.getResultSet();
                if (rs == null)
                {
                    return null;
                }
                try
                {
                    RowSet rowSet = new RowSet(this.connector, rs);
                    return rowSet;
                }
                finally
                {
                    rs.close();
                }
                **/
            }
            finally
            {
                stmt.close();
            }
        }
        catch (SQLException e)
        {
            closeConnectionAfterException(e);
            throw e;
        }
		return null;
    }

    String setStatement(PreparedStatement stmt, Object... objects)
                    throws SQLException
    {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < objects.length; i++)
        {
            stmt.setObject(i + 1, objects[i]);
            if (i > 0)
            {
                sb.append(',');
            }
            sb.append(objects[i]);
        }
        return sb.toString();
    }

    String setStatement(CallableStatement stmt, Object... objects)
                    throws SQLException
    {
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < objects.length; i++)
        {
            stmt.setObject(i + 1, objects[i]);
            if (i > 0)
            {
                sb.append(',');
            }
            sb.append(objects[i]);
        }
        return sb.toString();
    }
}
